package org.compiere.process;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import javax.swing.JFileChooser;
import javax.swing.JList;

import jxl.Workbook;
import jxl.format.Border;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

//import jxl.Range;
//import jxl.Workbook;
//import jxl.format.Alignment;
//import jxl.format.Border;
//import jxl.format.BorderLineStyle;
//import jxl.format.CellFormat;
//import jxl.format.Colour;
//import jxl.format.Format;
//import jxl.format.VerticalAlignment;
//import jxl.write.Label;
//import jxl.write.Number;
//import jxl.write.NumberFormats;
//import jxl.write.WritableCellFormat;
//import jxl.write.WritableFont;
//import jxl.write.WritableSheet;
//import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBCSACRInputFormAppend2Ext;
import org.compiere.model.MBCSACRInputFormAppend3_1;
import org.compiere.model.MBCSACRInputFormAppend3_1Ext;
import org.compiere.model.MBCSACRInputFormAppend4;
import org.compiere.model.MBCSACRInputFormCompany;
import org.compiere.model.MBCSFRInputForm;
import org.compiere.model.MBCSFRInputFormColumn;
import org.compiere.model.MBCSFRInputFormCompany;
import org.compiere.model.MBCSFRInputFormLine;
import org.compiere.model.MBCSFRInputFormPeriod;
import org.compiere.model.MBPartner;
import org.compiere.model.MBPartnerLocation;
import org.compiere.model.MLocation;
import org.compiere.model.MOrg;
import org.compiere.model.MPeriod;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;
import org.utils.*;

public class BCS_FReport extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private BigDecimal PeriodFrom = null;
	private MPeriod mperiod = null;
	private BigDecimal PeriodTo = null;
	private MPeriod mperiodTo = null;
	private BigDecimal Year = null;
	private BigDecimal Version = null;
	
	private int iYear = 0;
	private boolean isConsolidation = false;
	private BigDecimal C_BPartner = null;
	private String Application = "";

	private static CLogger s_log = CLogger.getCLogger(BCS_ACReport.class);
	/** */
	private String path = "";

	private int AD_Org_ID = -1;
	private int AD_Client_ID = -1;
	
	HSSFWorkbook wb = null;
	String Mother = "";
	String Address = "";

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("PeriodFrom")) {
				PeriodFrom = (BigDecimal) para[i].getParameter();
			}else if (name.equals("PeriodTo")) {
				PeriodTo = (BigDecimal) para[i].getParameter();
			}else if (name.equals("C_Year_ID")) {
				Year = (BigDecimal) para[i].getParameter();
				iYear = Integer.parseInt(para[i].getInfo());
			}else if (name.equals("BCS_Version")) {
				Version = (BigDecimal) para[i].getParameter();
			} else if (name.equals("isConsolidation")) {
				isConsolidation = ((String) para[i].getParameter()).equals("Y");
			} else if (name.equals("C_BPartner_ID")) {
				C_BPartner = (BigDecimal) para[i].getParameter();
			}
			else
				
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		// AD_Client_ID = getAD_Client_ID();
		// AD_Org_ID = getAD_Org_ID();

		path = Util.localFilePath;
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {
		if (isConsolidation)
			return isConsolidation();
		else
			return isNotConsolidation();
	}
	
	

	protected String isNotConsolidation() throws Exception {
		String result = "";
		MBCSFRInputFormPeriod period = getMBCSFRInputFormPeriod(PeriodFrom,
				PeriodTo, Year, Version);
		
		List<MBCSFRInputFormCompany> companies = getMBCSFRInputFormCompany(period.getBCS_FRInputForm_Period_ID(), C_BPartner);

		
		if (period == null || companies.size() == 0)
			return Msg.translate(m_ctx,
					"По данным параметрам данные отсутствуют.");

	
		
		// ---------------------------------------------------------------
		// Write the output to a file
		
		String file = "BCS_FReport_ind_tmpl.xlsx";
		String filePath = Util.localFilePath + file;
//		String file = Util.localFilePath +"test.xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));

		InputStream inputStream = entry.getInputStream();  
		XSSFWorkbook wb = new XSSFWorkbook(inputStream);
		
		Mother  = Env.getContext(Env.getCtx(), "#AD_Org_Name");
		mperiod = new MPeriod(Env.getCtx(), PeriodFrom.intValue(), null);
		mperiodTo = new MPeriod(Env.getCtx(), PeriodTo.intValue(), null);
		
		MBPartner bp = new MBPartner(m_ctx, C_BPartner.intValue(), get_TrxName());
		MBPartnerLocation[] Locations =  bp.getLocations(false);
		if(Locations.length > 0)
		  Address = 
		  	excNull(Locations[0].getC_Location().getCity()) + ", "
		  	+ excNull(Locations[0].getC_Location().getAddress1())	+ " "  
		  	+ excNull(Locations[0].getC_Location().getAddress2()) 	+ " "
		  	+ excNull(Locations[0].getC_Location().getAddress3()) 	+ " "
		  	+ excNull(Locations[0].getC_Location().getAddress4())	+ " "
		  ;
		

		List<MBCSFRInputForm> ListOfForm = getMBCSFRInputForm(companies);
		//read lines
		List<MBCSFRInputFormLine> ListOfLines = getMBCSFRInputFormLine(ListOfForm);
		//read columns
		List<MBCSFRInputFormColumn> ListOfColumns = getMBCSFRInputFormColumn(ListOfLines);

		List<String> namesOfSheet = new ArrayList<String>();
		int count = wb.getNumberOfSheets();
		for(int i=0; i < count; i++)
			namesOfSheet.add(wb.getSheetName(i));

		for(int i=0; i < count; i++){
			int row = 0;
			String s = namesOfSheet.get(i);
			int diff = s.indexOf(" ");
			if(diff == -1)
				continue;
			s = s.substring(0, diff);
			
			int code = -1;
			
			try{
				code = Integer.parseInt(s);
			}catch(NumberFormatException e){
//				s_log.log(Level.SEVERE, e.getMessage());
				continue;
			}
			
			XSSFSheet sheet = wb.getSheet(namesOfSheet.get(i));
//			XSSFSheet sheet = wb.getSheetAt(code);
			switch(code){
			case	1:
				print1(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	2:
				print2(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	3:
				print3(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	4:
				print4(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	5:
				print5(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	6:
				print6(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	7:
				print7(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	8:
				print8(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	9:
				print9(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	10:
				print10(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	11:
				print11(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	12:
				print12(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	13:
				print13(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;						
			case	14:
				print14(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	15:
				print15(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	16:
				print16(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	17:
				print17(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	18:
				print18(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	19:
				print19(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	20:
				print20(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	21:
				print21(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	22:
				print22(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	23:
				print23(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			}
			
			
		}		
		
		
		
		wb.setForceFormulaRecalculation(true);
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		if(!Env.isWindows())
			Util.launchFile(new File(filePath));
		else{
			try {
				String command = "excel \"" + filePath + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return Msg.translate(m_ctx, "Success");

	}
	
	
	
	
	protected String isConsolidation() throws Exception {

		MBCSFRInputFormPeriod period = getMBCSFRInputFormPeriod(PeriodFrom,
				PeriodTo, Year, Version);
		
		if (period == null)
			return Msg.translate(m_ctx,
					"По данным параметрам данные отсутствуют.");

	
		
		// ---------------------------------------------------------------
		// Write the output to a file
		
		String file = "BCS_FReport_tmpl.xlsx";
		String filePath = Util.localFilePath + file;
//		String file = Util.localFilePath +"test.xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));
		

		InputStream inputStream = entry.getInputStream();  
		XSSFWorkbook wb = new XSSFWorkbook(inputStream);
		
		
		//--------------------------------------------------------------------------
		//let's do it
		
		List<MBCSFRInputFormCompany> companies = getMBCSFRInputFormCompany(period
				.getBCS_FRInputForm_Period_ID(), null);

		Mother  = Env.getContext(Env.getCtx(), "#AD_Org_Name");
		mperiod = new MPeriod(Env.getCtx(), PeriodFrom.intValue(), null);
		mperiodTo = new MPeriod(Env.getCtx(), PeriodTo.intValue(), null);
		
		if(companies.size() > 0){
			for(MBCSFRInputFormCompany c: companies){
			MBPartner bp = (MBPartner) c.getC_BPartner();
			if(bp.getAD_OrgBP_ID_Int() > 0){
//				C_BPartner = bp.get_ID();
				MBPartnerLocation[] Locations =  bp.getLocations(false);
				if(Locations.length > 0)
				  Address = 
				  	excNull(Locations[0].getC_Location().getCity()) + ", "
				  	+ excNull(Locations[0].getC_Location().getAddress1())	+ " "  
				  	+ excNull(Locations[0].getC_Location().getAddress2()) 	+ " "
				  	+ excNull(Locations[0].getC_Location().getAddress3()) 	+ " "
				  	+ excNull(Locations[0].getC_Location().getAddress4())	+ " "
				  ;
			}
			}
		}
		
		
//		MBCSACRInputFormAppend1 ListOfAppend1 = getMBCSACRInputFormAppend1(companies
		List<MBCSFRInputForm> ListOfForm = getMBCSFRInputForm(companies);
		//read lines
		List<MBCSFRInputFormLine> ListOfLines = getMBCSFRInputFormLine(ListOfForm);
		//read columns
		List<MBCSFRInputFormColumn> ListOfColumns = getMBCSFRInputFormColumn(ListOfLines);

		List<String> namesOfSheet = new ArrayList<String>();
		int count = wb.getNumberOfSheets();
		for(int i=0; i < count; i++)
			namesOfSheet.add(wb.getSheetName(i));

		for(int i=0; i < count; i++){
			int row = 0;
			String s = namesOfSheet.get(i);
			int diff = s.indexOf(" ");
			if(diff == -1)
				continue;
			s = s.substring(0, diff);
			
			int code = -1;
			
			try{
				code = Integer.parseInt(s);
			}catch(NumberFormatException e){
//				s_log.log(Level.SEVERE, e.getMessage());
				continue;
			}
			
			XSSFSheet sheet = wb.getSheet(namesOfSheet.get(i));
//			XSSFSheet sheet = wb.getSheetAt(code);
			switch(code){
			case	1:
				print1(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	2:
				print2(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	3:
				print3(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	4:
				print4(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	5:
				print5(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	6:
				print6(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	7:
				print7(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	8:
				print8(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	9:
				print9(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	10:
				print10(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	11:
				print11(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	12:
				print12(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	13:
				print13(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;						
			case	14:
				print14(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	15:
				print15(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	16:
				print16(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;				
			case	17:
				print17(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	18:
				print18(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	19:
				print19(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;
			case	20:
				print20(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	21:
				print21(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	22:
				print22(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			case	23:
				print23(companies, ListOfForm, ListOfLines, ListOfColumns, code, sheet);
				break;		
			}
			
			
		}		
		
		

		//--------------------------------------------------------------------------
		
		wb.setForceFormulaRecalculation(true);
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		if(!Env.isWindows())
			Util.launchFile(new File(filePath));
		else{
			try {
				String command = "excel \"" + filePath + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return Msg.translate(m_ctx, "Success");
	}

	private void print1(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		
		
		Row R;
		Cell C;
		
		//write caption--------
			R = sheet.getRow(4);
			if(R != null){
				C = R.getCell(1);
				if(C == null)
					C = R.createCell(1);
				
				C.setCellValue(Mother);
			}
			String period = "по состоянию на ";
			Calendar c = Calendar.getInstance();
			c.setTime(mperiodTo.getEndDate());
			period += c.get(c.DAY_OF_MONTH) + " ";
			period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
			period += c.get(c.YEAR) + " года.";

			R = sheet.getRow(14);
			if(R != null){
				C = R.getCell(0);
				if(C ==	null)
					C = R.createCell(0);
				
				C.setCellValue(period);
			}
			
			R = sheet.getRow(11);
			if(R != null){
				C = R.getCell(1);
				if(C ==	null)
					C = R.createCell(1);
				
				C.setCellValue(Address);
			}
				
			
			
			
		//---------------------
		
		int iColumn = 2;
		for(MBCSFRInputFormCompany company: companies){
//			iColumn++;
//			iColumn++;
			
//			int bp = company.getC_BPartner_ID();
//			MBPartner mbp = new MBPartner(Env.getCtx(), bp, get_TrxName());
//			String bp_name = mbp.getName();
//			R = sheet.getRow(16);
//			if(R != null){
//				C = R.getCell(iColumn);
//				C.setCellValue(bp_name);
//			}
			if(!isConsolidation)
				iColumn = 2;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//standart
				iColumn = 2;
				break;
			case	1000084:	//зерде
				iColumn = 4;
				break;
			case	1000022:	//цэк
				iColumn = 6;
				break;		
			case	1000021:	//нит
				iColumn = 8;
				break;		
			case	1000018:	//казсанет
				iColumn = 10;
				break;	
			case	1000019:	//каз иссл. ист
				iColumn = 12;
				break;
			case	2000086:	//нпц
				iColumn = 14;
				break;		
			case	1000020:	//межд унив
				continue;

				//				iColumn = 16;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					int iRow = 0;
					if(!isConsolidation)
						iRow = 15;
					else
						iRow = 18;
					for(MBCSFRInputFormLine line : lines){
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
							
							iRow++;
							if(iRow == 49)
								iRow++;
						
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									
									
									
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);
									
									icolumn++;
									

								}
							}
						}
					}
					
				}
			}
		}
		
		
	}

	private void print2(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(7);
			if(C == null)
				C = R.createCell(7);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(7);
			if(C ==	null)
				C = R.createCell(7);
			
			C.setCellValue(period);
		}
		
		
	//---------------------
		
		int iRow = 2;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде	
				iRow = 8;
				break;
			case	1000084:	//зерде	
				iRow = 19;
				break;
			case	1000022:	//цэк	
				iRow = 26;
				break;		
			case	1000021:	//нит	
				iRow = 33;
				break;		
			case	1000018:	//казсанет	
				iRow = 40;
				break;	
			case	2000086:	//каз пресс центр
				iRow = 54;
				break;		
			case	1000019:	//каз иссл. ист	
				iRow = 47;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 16;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 3;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

									if(icolumn == 10)
										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print3(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		
		//write caption--------
		R = sheet.getRow(4);
		if(R != null){
			C = R.getCell(0);
			if(C == null)
				C = R.createCell(0);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(6);
		if(R != null){
			C = R.getCell(0);
			if(C ==	null)
				C = R.createCell(0);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		
		int iColumn = 2;
		for(MBCSFRInputFormCompany company: companies){
//			iColumn++;
//			iColumn++;
			
//			int bp = company.getC_BPartner_ID();
//			MBPartner mbp = new MBPartner(Env.getCtx(), bp, get_TrxName());
//			String bp_name = mbp.getName();
//			R = sheet.getRow(16);
//			if(R != null){
//				C = R.getCell(iColumn);
//				C.setCellValue(bp_name);
//			}
			if(!isConsolidation)
				iColumn = 2;
			else			
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iColumn = 2;
				break;
			case	1000084:	//зерде
				iColumn = 4;
				break;
			case	1000022:	//цэк
				iColumn = 6;
				break;		
			case	1000021:	//нит
				iColumn = 8;
				break;		
			case	1000018:	//казсанет
				iColumn = 10;
				break;	
			case	2000086:
				iColumn = 14;
				break;		
			case	1000019:	//каз иссл. ист
				iColumn = 12;
				break;
			case	1000020:	//межд унив
				iColumn = 16;
				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
					int iRow = 0;
					if(!isConsolidation)
						iRow = 7;
					else
						iRow = 10;
					for(MBCSFRInputFormLine line : lines){
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
							
							iRow++;
//							if(iRow == 49)
//								continue;
						
							if(isConsolidation && company.getC_BPartner_ID() == 1000020 && iRow != 44)
								continue;
							
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									
									
									if(company.getC_BPartner_ID() == 1000020 && iRow == 44){
										
										R = sheet.getRow(21);
										if(R == null)
											R = sheet.createRow(21);
										
										C = R.getCell(icolumn);
										if(C == null)
											C = R.createCell(icolumn);
										
										String formula = "";
										try{
											formula = C.getCellFormula();
										}catch(Exception e){}

										
										int value = 0;
										try{
											if(column.getValue1() == null || column.getValue1().equals(""))
												value = 0;
											else{	
												BigDecimal b = new BigDecimal(column.getValue1());
												b = b.multiply(new BigDecimal(0.3335));
												value = b.intValue();
											}
										}catch(Exception e){
											System.out.print(e.toString());
											value = 0;
										}
										
										if(formula.equals(""))
											C.setCellValue(value);
										else
											C.setCellFormula(formula + "+" + value);
										//System.out.println(column.getValue1());
										icolumn++;
										
										continue;
									}
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
									String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
										
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
						}
					}
					
				}
			}
		}
		
		
	}	
	
	private void print4(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(12);
			if(C == null)
				C = R.createCell(12);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(12);
			if(C ==	null)
				C = R.createCell(12);
			
			C.setCellValue(period);
		}
		
		
	//---------------------
		
		int iRow = 0;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 7;
				break;
			case	1000084:	//зерде
				iRow = 20;
				break;
			case	1000022:	//цэк
				iRow = 29;
				break;		
			case	1000021:	//нит
				iRow = 38;
				break;		
			case	1000018:	//казсанет
				iRow = 47;
				break;	
			case	2000086:
				iRow = 65;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 56;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 16;
//				break;
			default : continue;
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 8;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

									if(icolumn == 15)
										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
									String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}

									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}	
	
	
	private void print5(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		
		//write caption--------
		R = sheet.getRow(5);
		if(R != null){
			C = R.getCell(0);
			if(C == null)
				C = R.createCell(0);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(8);
		if(R != null){
			C = R.getCell(0);
			if(C ==	null)
				C = R.createCell(0);
			
			C.setCellValue(period);
		}
		
		
	//---------------------
		
		int iColumn = 2;
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iColumn = 2;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iColumn = 2;
				break;
			case	1000084:	//зерде	//зерде
				iColumn = 4;
				break;
			case	1000022:	//цэк	//цэк
				iColumn = 6;
				break;		
			case	1000021:	//нит	//нит
				iColumn = 8;
				break;		
			case	1000018:	//казсанет	//казсанет
				iColumn = 10;
				break;	
			case	2000086:	//каз пресс центр
				iColumn = 14;
				break;		
			case	1000019:	//каз иссл. ист	//каз исслед инст
				iColumn = 12;
				break;
			case	1000020:	//межд унив	//межд унив
				continue;
//				iColumn = 16;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
					int iRow = 0;
					if(!isConsolidation)
						iRow = 7;
					else
						iRow = 11;
					for(MBCSFRInputFormLine line : lines){
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
							
							iRow++;
//							if(iRow == 49)
//								continue;
						
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
									String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}


									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
						}
					}
					
				}
			}
		}
		
		
	}	
	
	private void print6(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(7);
			if(C == null)
				C = R.createCell(7);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(7);
			if(C ==	null)
				C = R.createCell(7);
			
			C.setCellValue(period);
		}
		
		
	//---------------------
		
		int iColumn = 0;
		for(MBCSFRInputFormCompany company: companies){
			
			if(!isConsolidation)
				iColumn = 2;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iColumn = 2;
				break;
			case	1000084:	//зерде	//зерде
				iColumn = 9;
				break;
			case	1000022:	//цэк	//цэк
				iColumn = 16;
				break;		
			case	1000021:	//нит	//нит
				iColumn = 23;
				break;		
			case	1000018:	//казсанет	//казсанет
				iColumn = 30;
				break;	
			case	2000086:	//каз пресс центр
				iColumn = 44;
				break;		
			case	1000019:	//каз иссл. ист	//каз исслед инст
				iColumn = 37;
				break;
			case	1000020:	//межд унив	//межд унив
				continue;
//				iColumn = 51;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
					int iRow = 7;
					for(MBCSFRInputFormLine line : lines){
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
							
							iRow++;
//							if(iRow == 49)
//								continue;
						
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
									String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}


									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
						}
					}
					
				}
			}
		}
		
		
	}
	
	
	private void print7(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		
		//write caption--------
		R = sheet.getRow(4);
		if(R != null){
			C = R.getCell(0);
			if(C == null)
				C = R.createCell(0);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(6);
		if(R != null){
			C = R.getCell(0);
			if(C ==	null)
				C = R.createCell(0);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		
		int iColumn = 0;
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iColumn = 2;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iColumn = 2;
				break;
			case	1000084:	//зерде	//зерде
				iColumn = 9;
				break;
			case	1000022:	//цэк	//цэк
				iColumn = 16;
				break;		
			case	1000021:	//нит	//нит
				iColumn = 23;
				break;		
			case	1000018:	//казсанет	//казсанет
				iColumn = 30;
				break;	
			case	2000086:	//каз пресс центр
				iColumn = 44;
				break;		
			case	1000019:	//каз иссл. ист	//каз исслед инст
				iColumn = 37;
				break;
			case	1000020:	//межд унив	//межд унив
				continue;
//				iColumn = 51;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
					int iRow = 0;
					if(!isConsolidation)
						iRow = 8;
					else
						iRow = 11;
					for(MBCSFRInputFormLine line : lines){
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
							
							iRow++;
//							if(iRow == 49)
//								continue;
						
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
									String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}


									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print8(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;

		double summ1 = .0;
		double summ2 = .0;

		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(3);
			if(C == null)
				C = R.createCell(3);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(2);
			if(C ==	null)
				C = R.createCell(2);
			
			C.setCellValue(period);
		}
		
	//---------------------		
		int iRow = 0;
		
		if(isConsolidation){
			iRow = 19;
		

			for(int i=19; i<60; i++){
				R = sheet.getRow(i);
				if(R != null)
					sheet.removeRow(R);
			}
		
		List<comp> list = new ArrayList<comp>();
		
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000084)
				list.add(new comp(company.get_ID(), 1000084));
			
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000022)
				list.add(new comp(company.get_ID(), 1000022));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000021)
				list.add(new comp(company.get_ID(), 1000021));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000018)
				list.add(new comp(company.get_ID(), 1000018));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000019)
				list.add(new comp(company.get_ID(), 1000019));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 2000086)
				list.add(new comp(company.get_ID(), 2000086));
		}
		
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000020)
				list.add(new comp(company.get_ID(), 1000020));
		}
		
	
		String f = "";
		String f2 = "";
		
		for(comp company: list){

			if(!isConsolidation)
				iRow = 9;
//			else
//				iRow = 19;
//			switch(company.bp){
//			case	1000000:	//зерде
//				iRow = 8;
//				break;
//			case	1000084:	//зерде
//				iRow = 21 ;//+ countTotal;
//				break;
//			case	1000022:	//цэк
//				iRow = 27 + countTotal;
//				break;		
//			case	1000021:	//нит
//				iRow = 33 + countTotal;
//				break;		
//			case	1000018:	//казсанет
//				iRow = 39 + countTotal;
//				break;	
//			case	1000019:	//каз иссл. ист
//				iRow = 45 + countTotal;
//				break;		
//			case	2000086:	//каз пресс. центр
//				iRow = 51 + countTotal;
//				break;
//			case	1000020:	//межд унив
//				continue;
////				iRow = 39;
////				break;				
//			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
//			
//			
			R = sheet.getRow(iRow);
			if(R == null)
				R = sheet.createRow(iRow);

			C = R.getCell(1);
			if(C == null)
				C = R.createCell(1);
				
				C.setCellStyle(sheet.getRow(15).getCell(1).getCellStyle());
			
//			for(MBCSFRInputFormCompany cc: companies)
//				if(cc.get_ID() == company.id)

				MBPartner bp = new MBPartner(Env.getCtx(), company.bp, null);
						C.setCellValue(bp.getName());
	
				iRow++;

			R = sheet.getRow(iRow);
			for(int j=1; j<5; j++){
				if(R == null)
					R = sheet.createRow(iRow);
				try{
					if(!isConsolidation)
						R.createCell(j).setCellStyle(sheet.getRow(8).getCell(j).getCellStyle());
					else
						R.createCell(j).setCellStyle(sheet.getRow(7).getCell(j).getCellStyle());
				}catch(Exception e){}
			}

				C = R.getCell(1);
				if(C != null)
					C.setCellValue("№");
				C = R.getCell(2);
				if(C != null)
					C.setCellValue("Наименование показателей");			
				C = R.getCell(3);
				if(C != null)
					C.setCellValue("Сумма");

			iRow++;
			

			boolean noRow = false;
			
			for(MBCSFRInputForm form: forms){
				if(company.id == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
					noRow = true;
					int iColumn = 3;

					int isGroup1 = 0;
					int isGroup2 = 0;
					boolean isTwo = false;
					
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									if(column.getValue1() != null && column.getValue1().equals("Доходы по основным видам деятельности всего") && isGroup1 == 0)
										isGroup1 = line.get_ID();
									if(column.getValue1() != null && column.getValue1().equals("Доходы от неосновной деятельности всего") && isGroup2 == 0)
										isGroup2 = line.get_ID();
								}
							}
						}
					}
					
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
							R = sheet.getRow(iRow);
							for(int j=1; j<5; j++){
								if(R == null)
									R = sheet.createRow(iRow);
								try{
									if(!isConsolidation)
										R.createCell(j).setCellStyle(sheet.getRow(9).getCell(j).getCellStyle());
									else
										R.createCell(j).setCellStyle(sheet.getRow(8).getCell(j).getCellStyle());
								}catch(Exception e){}
							}

							
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
									String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	

											String ss = column.getValue1();
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
//											C.setCellValue(value);
											if(isGroup1 == line.get_ID() && value != 1){
												summ1 += value;
												if(f.equals(""))
													f = "D"+(iRow+1);
												else
													f += "+D"+(iRow+1);
											}
											
											if(isGroup2 == line.get_ID() && value != 2){
												summ2 += value;
												if(f2.equals(""))
													f2 = "D"+(iRow+1);
												else
													f2 += "+D"+(iRow+1);
											}
											
											if(formula.equals(""))
												C.setCellValue(value);
											else
												C.setCellFormula(formula + "+" + value);
											

										}
									}catch(Exception e){
										String ss = column.getValue1();
										if(formula.equals(""))
//											if(isGroup1 == line.get_ID() || isGroup2 == line.get_ID())
//												C.setCellValue(column.getValue1().toUpperCase());
//											else
												C.setCellValue(column.getValue1());
										else
											C.setCellFormula(formula + "+" + column.getValue1());
										

									}
									
									icolumn--;	

								}
							}
							iRow++;
						}
					}
					
				}
			}
			
			if(!noRow){
				for(int i=0; i<3; i++){
					R = sheet.getRow(iRow);
					for(int j=1; j<5; j++){
						if(R == null)
							R = sheet.createRow(iRow);
						try{
							if(!isConsolidation)
								R.createCell(j).setCellStyle(sheet.getRow(9).getCell(j).getCellStyle());
							else
								R.createCell(j).setCellStyle(sheet.getRow(8).getCell(j).getCellStyle());
						}catch(Exception e){}
					}
					C = R.getCell(1);
					if(C != null)
						if(i==0)
							C.setCellValue("1");
						if(i==1)
							C.setCellValue("2");
						
					C = R.getCell(2);
					if(C != null)
						if(i==0)
							C.setCellValue("Доходы по основным видам деятельности всего");
						if(i==1)
							C.setCellValue("Доходы от неосновной деятельности всего");
						if(i==2)
							C.setCellValue("ИТОГО ДОХОДОВ");
						
					if(i == 0)	
						if(f.equals(""))
							f = "D"+(iRow+1);
						else
							f += "+D"+(iRow+1);
					if(i == 1)
						if(f2.equals(""))
							f2 = "D"+(iRow+1);
						else
							f2 += "+D"+(iRow+1);
						
					iRow++;
				}
			}	
			
			iRow += 2;
				
		
		R = sheet.getRow(8);
		C = R.getCell(3);
		if(!f.equals(""))
			C.setCellFormula(f);
		
		R = sheet.getRow(9);
		C = R.getCell(3);
		if(!f.equals(""))
			C.setCellFormula(f2);
		
		R = sheet.getRow(10);
		C = R.getCell(3);
		C.setCellValue(summ1 + summ2);
			}
		}else{//isNotConsidation
			
			
			
			
			
			
			
			
			
			
			
			 
			iRow = 9;
			boolean noRow = false;
			int comp = 0;
			for(MBCSFRInputFormCompany co: companies)
				if(co.getC_BPartner_ID() == C_BPartner.intValue())
					comp = co.get_ID();
			
			for(MBCSFRInputForm form: forms){
				if(comp == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					for(MBCSFRInputFormLine line : lines){
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							noRow = true;
						}
						
					}
					
				}
				
			}
			
			if(!noRow)
				return;
			
			for(int i=9; i<100; i++){
				R = sheet.getRow(i);
				if(R != null)
					sheet.removeRow(R);
			}		
			
			
							

				

				for(MBCSFRInputForm form: forms){
					if(comp == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
						
						noRow = true;
						int iColumn = 3;
						
						for(MBCSFRInputFormLine line : lines){
							
							int icolumn = iColumn;
							if(line.getBCS_FRInputForm_ID() == form.get_ID()){
								
								R = sheet.getRow(iRow);
								for(int j=1; j<5; j++){
									if(R == null)
										R = sheet.createRow(iRow);
									try{
										R.createCell(j).setCellStyle(sheet.getRow(8).getCell(j).getCellStyle());
									}catch(Exception e){}
								}

								
								
								for(MBCSFRInputFormColumn column: columns){
									if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

										R = sheet.getRow(iRow);
										if(R == null)
											R = sheet.createRow(iRow);
										
										C = R.getCell(icolumn);
										if(C == null)
											C = R.createCell(icolumn);
										
										int value = 0;
										try{
											if(column.getValue1() == null || column.getValue1().equals(""))
												value = 0;
											else{	

												String ss = column.getValue1();
												BigDecimal b = new BigDecimal(column.getValue1());
												value = b.intValue();
												summ1 += value;
												C.setCellValue(value);

											}
										}catch(Exception e){
											String ss = column.getValue1();
											C.setCellValue(column.getValue1());
										}
										
										icolumn--;	

									}
								}
								iRow++;
							}
						}
						
					}
				}
			
			R = sheet.getRow(8);
			sheet.removeRow(R);
			sheet.shiftRows(9, iRow, -1);

			iRow++;
			
			R = sheet.getRow(iRow);
			if(R == null)
				R = sheet.createRow(iRow);
			
			C = R.getCell(1);
			if(C == null)
				C = R.createCell(1);
			C.setCellValue("Руководитель ___________________________       ");
			C = R.getCell(3);
			if(C == null)
				C = R.createCell(3);
			C.setCellValue("__________________");
			iRow++;
			
			R = sheet.getRow(iRow);
			if(R == null)
				R = sheet.createRow(iRow);
			
			C = R.getCell(1);
			if(C == null)
				C = R.createCell(1);
			C.setCellValue("            (фамилия, имя, отчетство)");
			C = R.getCell(3);
			if(C == null)
				C = R.createCell(3);
			C.setCellValue("(подпись)");
			iRow++;
            
			iRow++;
			
			R = sheet.getRow(iRow);
			if(R == null)
				R = sheet.createRow(iRow);
			
			C = R.getCell(1);
			if(C == null)
				C = R.createCell(1);
			C.setCellValue("Главный бухгалтер _________________________");
			C = R.getCell(3);
			if(C == null)
				C = R.createCell(3);
			C.setCellValue("__________________");
			iRow++;
			
			R = sheet.getRow(iRow);
			if(R == null)
				R = sheet.createRow(iRow);
			
			C = R.getCell(1);
			if(C == null)
				C = R.createCell(1);
			C.setCellValue("            (фамилия, имя, отчетство)");
			C = R.getCell(3);
			if(C == null)
				C = R.createCell(3);
			C.setCellValue("(подпись)");

			iRow++;
			R = sheet.getRow(iRow);
			if(R == null)
				R = sheet.createRow(iRow);
			
			C = R.getCell(1);
			if(C == null)
				C = R.createCell(1);
			C.setCellValue("Место печати");
			

			
			
		}
		
		
	}
	
	private void print9(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(10);
			if(C == null)
				C = R.createCell(10);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(10);
			if(C ==	null)
				C = R.createCell(10);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		
		int iColumn = 0;
		for(MBCSFRInputFormCompany company: companies){
			
			if(!isConsolidation)
				iColumn = 3;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iColumn = 3;
				break;
			case	1000084:	//зерде	//зерде
				iColumn = 14;
				break;
			case	1000022:	//цэк	//цэк
				iColumn = 25;
				break;		
			case	1000021:	//нит	//нит
				iColumn = 36;
				break;		
			case	1000018:	//казсанет	//казсанет
				iColumn = 47;
				break;	
			case	2000086:	//каз пресс центр
				iColumn = 69;
				break;		
			case	1000019:	//каз иссл. ист	//каз исслед инст
				iColumn = 58;
				break;
			case	1000020:	//межд унив	//межд унив
				continue;
//				iColumn = 80;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					int iRow = 0;					
					if(!isConsolidation)
						iRow = 8;
					else
						iRow = 8;

					for(MBCSFRInputFormLine line : lines){
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
							
							iRow++;
							if(iRow == 50)
								iRow += 5;
						
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
						}
					}
					
				}
			}
		}
		
		
	}
	
	
	private void print10(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(2);
			if(C == null)
				C = R.createCell(2);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(2);
			if(C ==	null)
				C = R.createCell(2);
			
			C.setCellValue(period);
		}
		
		
	//---------------------
		int iColumn = 0;
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iColumn = 2;
			else			
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iColumn =24;
				break;
			case	1000084:	//зерде	//зерде
				iColumn = 4;
				break;
			case	1000022:	//цэк	//цэк
				iColumn = 6;
				break;		
			case	1000021:	//нит	//нит
				iColumn = 8;
				break;		
			case	1000018:	//казсанет	//казсанет
				iColumn = 10;
				break;	
			case	2000086:	//каз пресс центр
				iColumn = 14;
				break;		
			case	1000019:	//каз иссл. ист	//каз исслед инст
				iColumn = 12;
				break;
			case	1000020:	//межд унив	//межд унив
				continue;
//				iColumn = 16;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
					int iRow = 8;
					for(MBCSFRInputFormLine line : lines){
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
							
							iRow++;
//							if(iRow == 49)
//								continue;
						
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
//											C.setCellValue(value);
											if(formula.equals(""))
												C.setCellValue(value);
											else
												C.setCellFormula(formula + "+" + value);

										}
									}catch(Exception e){
//										C.setCellValue(column.getValue1());
										if(formula.equals(""))
											C.setCellValue(value);
										else
											C.setCellFormula(formula + "+" + value);

										//										System.out.print(e.toString());
										value = 0;
									}
									
//									if(value != 0)
//										C.setCellValue(value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print11(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(3);
			if(C == null)
				C = R.createCell(3);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(3);
			if(C ==	null)
				C = R.createCell(3);
			
			C.setCellValue(period);
		}
		
		
	//---------------------
		
		int iColumn = 0;
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iColumn = 4;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iColumn = 4;
				break;
			case	1000084:	//зерде	//зерде
				iColumn = 5;
				break;
			case	1000022:	//цэк	//цэк
				iColumn = 6;
				break;		
			case	1000021:	//нит	//нит
				iColumn = 7;
				break;		
			case	1000018:	//казсанет	//казсанет
				iColumn = 8;
				break;	
			case	2000086:	//каз пресс центр
				iColumn = 10;
				break;		
			case	1000019:	//каз иссл. ист	//каз исслед инст
				iColumn = 9;
				break;
			case	1000020:	//межд унив	//межд унив
				continue;
//				iColumn = 11;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
					int iRow = 8;
					for(MBCSFRInputFormLine line : lines){
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
							
							iRow++;
//							if(iRow == 49)
//								continue;
						
							
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print12(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
//		sheet.shiftRows(21, 40, 10);
		
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(11);
			if(C == null)
				C = R.createCell(11);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(11);
			if(C ==	null)
				C = R.createCell(11);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		
		int iRow = 2;
		int countOfRows = 40;
		int count = 0;
		int countTotal = 0;
		
		
		
		List<comp> list = new ArrayList<comp>();
		
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000084)
				list.add(new comp(company.get_ID(), 1000084));
			
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000022)
				list.add(new comp(company.get_ID(), 1000022));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000021)
				list.add(new comp(company.get_ID(), 1000021));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000018)
				list.add(new comp(company.get_ID(), 1000018));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000019)
				list.add(new comp(company.get_ID(), 1000019));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 2000086)
				list.add(new comp(company.get_ID(), 2000086));
		}
		
		
		for(comp company: list){

			if(!isConsolidation)
				iRow = 9;
			else
			switch(company.bp){
			case	1000000:	//зерде
				iRow = 9;
				break;
			case	1000084:	//зерде
				iRow = 21 ;//+ countTotal;
				break;
			case	1000022:	//цэк
				iRow = 24 + countTotal;
				break;		
			case	1000021:	//нит
				iRow = 27 + countTotal;
				break;		
			case	1000018:	//казсанет
				iRow = 30 + countTotal;
				break;	
			case	1000019:	//каз иссл. ист
				iRow = 36 + countTotal;
				break;		
			case	2000086:	//каз пресс. центр
				iRow = 33 + countTotal;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 39;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			
			for(MBCSFRInputForm form: forms){
				if(company.id == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(company + ", " + form.getBCS_FRInputForm_Company_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 2;
					
					//create lines----------
					//iRow += count;
					count = 0;
					for(MBCSFRInputFormLine line : lines){
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							count++;
						}
					}
					
					//R = sheet.createRow(iRow+1);
					if(count > 2 && countOfRows > (iRow+1)){
						sheet.shiftRows(iRow+1, countOfRows, count-2);
						//set Style
						for(int i=0; i < count-2; i++){

							R = sheet.getRow(iRow+1+i);
							for(int j=1; j<15; j++){
								if(R == null)
									R = sheet.createRow(iRow+1+i);
								try{
									if(!isConsolidation)
										R.createCell(j).setCellStyle(sheet.getRow(9).getCell(j).getCellStyle());
									else
										R.createCell(j).setCellStyle(sheet.getRow(21).getCell(j).getCellStyle());
								}catch(Exception e){}
							}
						}
						//
						
					}
					countOfRows += count;
					countTotal += (count-2);
					
					
					
					
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
									String formula = "";
									
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
//											C.setCellValue(value);
											if(formula.equals(""))
												C.setCellValue(value);
											else
												C.setCellFormula(formula + "+" + value);

										}
									}catch(Exception e){
										System.out.print(e.toString());
//										C.setCellValue(column.getValue1());
										if(formula.equals(""))
											C.setCellValue(column.getValue1());
										else
											C.setCellFormula(formula + "+" + value);

									}
									
									icolumn++;	

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}	
	
	class comp{
		
		comp(int c, int b){
			this.id = c;
			this.bp = b;
		}
		
		int id = 0;
		int bp = 0;
	}
	
	private void print13(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(5);
			if(C == null)
				C = R.createCell(5);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(5);
			if(C ==	null)
				C = R.createCell(5);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 2;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 28;
				break;
			case	1000022:	//цэк
				iRow = 42;
				break;		
			case	1000021:	//нит
				iRow = 56;
				break;		
			case	1000018:	//казсанет
				iRow = 70;
				break;	
			case	2000086:
				iRow = 98;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 84;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 112;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 1;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}

	private void print14(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(5);
			if(C == null)
				C = R.createCell(5);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(5);
			if(C ==	null)
				C = R.createCell(5);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){
			
			if(!isConsolidation)
				iRow = 9;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 9;
				break;
			case	1000084:	//зерде
				iRow = 47;
				break;
			case	1000022:	//цэк
				iRow = 76;
				break;		
			case	1000021:	//нит
				iRow = 105;
				break;		
			case	1000018:	//казсанет
				iRow = 134;
				break;	
			case	2000086:
				iRow = 163;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 192;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 221;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 1;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print15(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(2);
			if(C == null)
				C = R.createCell(2);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(2);
			if(C ==	null)
				C = R.createCell(2);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 24;
				break;
			case	1000022:	//цэк
				iRow = 32;
				break;		
			case	1000021:	//нит
				iRow = 40;
				break;		
			case	1000018:	//казсанет
				iRow = 48;
				break;	
			case	2000086:
				iRow = 56;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 64;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 72;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 2;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print16(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(4);
			if(C == null)
				C = R.createCell(4);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(4);
			if(C ==	null)
				C = R.createCell(4);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 32;
				break;
			case	1000022:	//цэк
				iRow = 48;
				break;		
			case	1000021:	//нит
				iRow = 64;
				break;		
			case	1000018:	//казсанет
				iRow = 80;
				break;	
			case	2000086:
				iRow = 96;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 112;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 128;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 1;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}
	

	private void print17(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(4);
			if(C == null)
				C = R.createCell(4);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(4);
			if(C ==	null)
				C = R.createCell(4);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 32;
				break;
			case	1000022:	//цэк
				iRow = 48;
				break;		
			case	1000021:	//нит
				iRow = 64;
				break;		
			case	1000018:	//казсанет
				iRow = 80;
				break;	
			case	2000086:
				iRow = 96;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 112;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 128;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 1;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}

	private void print18(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(2);
			if(C == null)
				C = R.createCell(2);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(2);
			if(C ==	null)
				C = R.createCell(2);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 22;
				break;
			case	1000022:	//цэк
				iRow = 29;
				break;		
			case	1000021:	//нит
				iRow = 36;
				break;		
			case	1000018:	//казсанет
				iRow = 43;
				break;	
			case	2000086:
				iRow = 50;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 57;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 64;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 2;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
//											C.setCellValue(value);
											if(formula.equals(""))
												C.setCellValue(value);
											else
												C.setCellFormula(formula + "+" + value);

										}
									}catch(Exception e){
										if(formula.equals(""))
											C.setCellValue(column.getValue1());
										else
											C.setCellFormula(formula + "+" + value);

//										C.setCellValue(column.getValue1());
//										System.out.print(e.toString());
//										value = 0;
									}
									
//									C.setCellValue(value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print19(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
//		sheet.shiftRows(21, 40, 10);
		
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(5);
			if(C == null)
				C = R.createCell(5);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(5);
			if(C ==	null)
				C = R.createCell(5);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		
		int iRow = 2;
		int countOfRows = 59;
		int count = 0;
		int countTotal = 0;
		
		
		List<comp> list = new ArrayList<comp>();
		
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000084)
				list.add(new comp(company.get_ID(), 1000084));
			
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000022)
				list.add(new comp(company.get_ID(), 1000022));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000021)
				list.add(new comp(company.get_ID(), 1000021));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000018)
				list.add(new comp(company.get_ID(), 1000018));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 1000019)
				list.add(new comp(company.get_ID(), 1000019));
		}
		for(MBCSFRInputFormCompany company: companies){
			if(company.getC_BPartner_ID() == 2000086)
				list.add(new comp(company.get_ID(), 2000086));
		}
		
		for(comp company: list){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.bp){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 22;	//+ countTotal;
				break;
			case	1000022:	//цэк
				iRow = 28 + countTotal;
				break;		
			case	1000021:	//нит
				iRow = 34 + countTotal;
				break;		
			case	1000018:	//казсанет
				iRow = 40 + countTotal;
				break;	
			case	2000086:
				iRow = 46 + countTotal;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 52 + countTotal;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 58;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.id == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 2;
					
					//create lines----------
//					iRow += count;
					count = 0;
					for(MBCSFRInputFormLine line : lines){
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							count++;
						}
					}
					count--;
					
					//R = sheet.createRow(iRow+1);
					if(count > 2){
						sheet.shiftRows(iRow+1, countOfRows, count-1);
						//set Style
						for(int i=0; i < count-1; i++){

							R = sheet.getRow(iRow+1+i);
							for(int j=1; j<7; j++){
								if(R == null)
									R = sheet.createRow(iRow+1+i);
								try{
									if(!isConsolidation)
										R.createCell(j).setCellStyle(sheet.getRow(8).getCell(j).getCellStyle());
									else
										R.createCell(j).setCellStyle(sheet.getRow(22).getCell(j).getCellStyle());
								}catch(Exception e){}
							}
						}
						//
						
					}
					countOfRows += count;
					countTotal += count-1;
					
					
					
					MBCSFRInputFormLine Summary = null;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							if(Summary == null){
								Summary = line;
								continue;
							}
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
//											C.setCellValue(value);
											if(formula.equals(""))
												C.setCellValue(value);
											else
												C.setCellFormula(formula + "+" + value);

										}
									}catch(Exception e){
										System.out.print(e.toString());
//										C.setCellValue(column.getValue1());
										if(formula.equals(""))
											C.setCellValue(column.getValue1());
										else
											C.setCellFormula(formula + "+" + value);

//										System.out.println(column.getValue1());
									}
									
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
						
						//------------------- summary
						if(Summary != null){
							int icolumn = 2;
//							iRow++;
							
						for(MBCSFRInputFormColumn column: columns){
							if(column.getBCS_FRInputFormLine_ID() == Summary.get_ID()){
								

//								if(icolumn == 10)
//									continue;
								
//								System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
								
//								System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
								
								R = sheet.getRow(iRow);
								if(R == null)
									R = sheet.createRow(iRow);
								
								C = R.getCell(icolumn);
								if(C == null)
									C = R.createCell(icolumn);
								
																	String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
								
								int value = 0;
								try{
									if(column.getValue1() == null || column.getValue1().equals(""))
										value = 0;
									else{	
										BigDecimal b = new BigDecimal(column.getValue1());
										value = b.intValue();
//										C.setCellValue(value);
										if(formula.equals(""))
											C.setCellValue(value);
										else
											C.setCellFormula(formula + "+" + value);

									}
								}catch(Exception e){
									System.out.print(e.toString());
//									C.setCellValue(column.getValue1());
									if(formula.equals(""))
										C.setCellValue(column.getValue1());
									else
										C.setCellFormula(formula + "+" + value);

//									System.out.println(column.getValue1());
								}
								
								icolumn++;	

							}
						}
//						iRow++;
					}
					Summary = null;	
						
						
						
						
					
					
				}
			}
		}
		
		
	}	
	
	
	
	private void print20(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(4);
			if(C == null)
				C = R.createCell(4);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(4);
			if(C ==	null)
				C = R.createCell(4);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){
			
			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 24;
				break;
			case	1000022:	//цэк
				iRow = 34;
				break;		
			case	1000021:	//нит
				iRow = 44;
				break;		
			case	1000018:	//казсанет
				iRow = 54;
				break;	
			case	2000086:
				iRow = 64;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 74;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 84;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 4;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
//											C.setCellValue(value);
											if(formula.equals(""))
												C.setCellValue(value);
											else
												C.setCellFormula(formula + "+" + value);

										}
									}catch(Exception e){
//										C.setCellValue(column.getValue1());
										if(formula.equals(""))
											C.setCellValue(column.getValue1());
										else
											C.setCellFormula(formula + "+" + value);

//										System.out.print(e.toString());
//										value = 0;
									}
									
//									C.setCellValue(value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}

	private void print21(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(5);
			if(C == null)
				C = R.createCell(5);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(5);
			if(C ==	null)
				C = R.createCell(5);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 20;
				break;
			case	1000022:	//цэк
				iRow = 26;
				break;		
			case	1000021:	//нит
				iRow = 32;
				break;		
			case	1000018:	//казсанет
				iRow = 38;
				break;	
			case	2000086:
				iRow = 44;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 50;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 56;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 6;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
//											C.setCellValue(value);
											if(formula.equals(""))
												C.setCellValue(value);
											else
												C.setCellFormula(formula + "+" + value);

										}
									}catch(Exception e){
//										C.setCellValue(column.getValue1());
										if(formula.equals(""))
											C.setCellValue(column.getValue1());
										else
											C.setCellFormula(formula + "+" + value);

//										System.out.print(e.toString());
//										value = 0;
									}
									
//									C.setCellValue(value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print22(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(4);
			if(C == null)
				C = R.createCell(4);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(4);
			if(C ==	null)
				C = R.createCell(4);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 23;
				break;
			case	1000022:	//цэк
				iRow = 32;
				break;		
			case	1000021:	//нит
				iRow = 41;
				break;		
			case	1000018:	//казсанет
				iRow = 50;
				break;	
			case	2000086:
				iRow = 59;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 68;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 77;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 4;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
//											C.setCellValue(value);
											if(formula.equals(""))
												C.setCellValue(value);
											else
												C.setCellFormula(formula + "+" + value);

										}
									}catch(Exception e){
//										C.setCellValue(column.getValue1());
										if(formula.equals(""))
											C.setCellValue(column.getValue1());
										else
											C.setCellFormula(formula + "+" + value);

//										System.out.print(e.toString());
//										value = 0;
									}
									
//									C.setCellValue(value);
									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}
	
	private void print23(List<MBCSFRInputFormCompany> companies, List<MBCSFRInputForm> forms, List<MBCSFRInputFormLine> lines, 
			List<MBCSFRInputFormColumn> columns, int code, XSSFSheet sheet) {
		 
		Row R;
		Cell C;
		//write caption--------
		R = sheet.getRow(2);
		if(R != null){
			C = R.getCell(2);
			if(C == null)
				C = R.createCell(2);
			
			C.setCellValue(Mother);
		}
		String period = "по состоянию на ";
		Calendar c = Calendar.getInstance();
		c.setTime(mperiodTo.getEndDate());
		period += c.get(c.DAY_OF_MONTH) + " ";
		period += org.utils.DataUtils.getMonthNameRU(c.getTime()) + " ";
		period += c.get(c.YEAR) + " года.";

		R = sheet.getRow(1);
		if(R != null){
			C = R.getCell(2);
			if(C ==	null)
				C = R.createCell(2);
			
			C.setCellValue(period);
		}
		
		
	//---------------------		
		int iRow = 1;
		
		for(MBCSFRInputFormCompany company: companies){

			if(!isConsolidation)
				iRow = 8;
			else
			switch(company.getC_BPartner_ID()){
			case	1000000:	//зерде
				iRow = 8;
				break;
			case	1000084:	//зерде
				iRow = 20;
				break;
			case	1000022:	//цэк
				iRow = 25;
				break;		
			case	1000021:	//нит
				iRow = 30;
				break;		
			case	1000018:	//казсанет
				iRow = 35;
				break;	
			case	2000086:
				iRow = 40;
				break;		
			case	1000019:	//каз иссл. ист
				iRow = 45;
				break;
			case	1000020:	//межд унив
				continue;
//				iRow = 50;
//				break;				
			}
//			System.out.println(company.getC_BPartner_ID());//-------------------
			
			for(MBCSFRInputForm form: forms){
				if(company.get_ID() == form.getBCS_FRInputForm_Company_ID() && form.getCode() == code){
					
//					System.out.println(form.get_ID());//------------------
					
//					int iRow = 19;
					int iColumn = 1;
					for(MBCSFRInputFormLine line : lines){
						
						int icolumn = iColumn;
						if(line.getBCS_FRInputForm_ID() == form.get_ID()){
							
//							System.out.println("line: " + line.get_ID());//------------------
//							iRow++;
							for(MBCSFRInputFormColumn column: columns){
								if(column.getBCS_FRInputFormLine_ID() == line.get_ID()){

//									if(icolumn == 10)
//										continue;
									
//									System.out.println("iRow: " + iRow + "Icolumn: " + icolumn);
									
//									System.out.println("Icolumn: " + icolumn + "	column: " + column.get_ID() + " value: " + column.getValue1());//------------------
									
									R = sheet.getRow(iRow);
									if(R == null)
										R = sheet.createRow(iRow);
									
									C = R.getCell(icolumn);
									if(C == null)
										C = R.createCell(icolumn);
									
																		String formula = "";
									try{
										formula = C.getCellFormula();
									}catch(Exception e){}
									
									int value = 0;
									try{
										if(column.getValue1() == null || column.getValue1().equals(""))
											value = 0;
										else{	
											BigDecimal b = new BigDecimal(column.getValue1());
											value = b.intValue();
										}
									}catch(Exception e){
										System.out.print(e.toString());
										value = 0;
									}
									
//									C.setCellValue(value);
									if(formula.equals(""))
										C.setCellValue(value);
									else
										C.setCellFormula(formula + "+" + value);

									//System.out.println(column.getValue1());
									icolumn++;
									

								}
							}
							iRow++;
						}
					}
					
				}
			}
		}
		
		
	}
	
	
	protected MBCSFRInputFormPeriod getMBCSFRInputFormPeriod(BigDecimal _periodFrom, BigDecimal _periodTo,  BigDecimal year,  BigDecimal Version) {
		MBCSFRInputFormPeriod period = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_FRInputForm_Period_ID FROM adempiere.BCS_FRInputForm_Period WHERE PeriodFrom = '"
				+ _periodFrom + "' AND PeriodTo = '"+ _periodTo +"'  AND C_Year_ID = '" + year + "' AND BCS_Version = '" + Version + "'";

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				period = new MBCSFRInputFormPeriod(Env.getCtx(), rs.getInt(1),
						null);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return period;
	}
	
	protected List<MBCSFRInputFormCompany> getMBCSFRInputFormCompany(
			int MBCSFRInputFormPeriod_ID, BigDecimal c_bpartner) {
		List<MBCSFRInputFormCompany> company = new ArrayList<MBCSFRInputFormCompany>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_FRInputForm_Company_ID FROM adempiere.BCS_FRInputForm_Company \n WHERE BCS_FRInputForm_Period_ID = "
				+ MBCSFRInputFormPeriod_ID; 
		if(c_bpartner != null && c_bpartner.compareTo(new BigDecimal(0)) == 1)
				sql += " and C_BPartner_ID = " + c_bpartner;

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				company.add(new MBCSFRInputFormCompany(Env.getCtx(),
						rs.getInt(1), null));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return company;
	}
	
	private List<MBCSFRInputFormLine> getMBCSFRInputFormLine(
			List<MBCSFRInputForm> forms) {
		
		List<MBCSFRInputFormLine> lines = new ArrayList<MBCSFRInputFormLine>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSFRInputForm form: forms){
		
			sql = "SELECT BCS_FRInputFormLine_ID FROM adempiere.BCS_FRInputFormLine WHERE  BCS_FRInputForm_ID = " + form.get_ID()
					+ " order by BCS_FRInputFormLine_ID";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputFormLine line = new MBCSFRInputFormLine(Env.getCtx(), rs.getInt(1),	null);
					lines.add(line);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return lines;
	}
	

	private List<MBCSFRInputForm> getMBCSFRInputForm(
			List<MBCSFRInputFormCompany> companies) {
		
		List<MBCSFRInputForm> forms = new ArrayList<MBCSFRInputForm>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		for(MBCSFRInputFormCompany company: companies){
//			sql = "SELECT BCS_FRInputForm_ID FROM adempiere.BCS_FRInputForm WHERE  BCS_FRInputForm_Company_ID = " + company.get_ID();
			sql = "SELECT f.BCS_FRInputForm_ID, f.BCS_FRSetupInputForm_ID, s.name, s.code FROM adempiere.BCS_FRInputForm f	\n"
					+ "	inner join adempiere.BCS_FRSetupInputForm s on s.BCS_FRSetupInputForm_ID = f.BCS_FRSetupInputForm_ID	\n"
					+ "	WHERE  BCS_FRInputForm_Company_ID = " + company.get_ID();

	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputForm form = new MBCSFRInputForm(Env.getCtx(), rs.getInt(1),	null);
					form.setName(rs.getString(3));
					form.setCode(rs.getInt(4));
					forms.add(form);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
//		

		return forms;
	}
	
	private List<MBCSFRInputFormColumn> getMBCSFRInputFormColumn(
			List<MBCSFRInputFormLine> lines) {
		
		List<MBCSFRInputFormColumn> columns = new ArrayList<MBCSFRInputFormColumn>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSFRInputFormLine line: lines){
		
			sql = "SELECT BCS_FRInputFormColumn_ID FROM adempiere.BCS_FRInputFormColumn WHERE  BCS_FRInputFormLine_ID = " + line.get_ID()
					+ " order by seqno ";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputFormColumn column = new MBCSFRInputFormColumn(Env.getCtx(), rs.getInt(1),	null);
					columns.add(column);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return columns;
	}

	protected String excNull(Object o){
		String result = "";
			if(o != null && o instanceof String)
				result = (String) o;
			
		return result;
	}
	
}

